package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Type;
import com.xxxy.jsq.model.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TypeDao implements BaseDao<Type> {
    private C3P0Utils db = null;

    public TypeDao() {
        this.db = new C3P0Utils();
    }
//
    @Override
    public String add(Type model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("insert into pa_type(type_name, type_count, type_flag, type_years) ");
        sqlStr.append("values(?,?,?,?) ");
        conn = db.getConnection();
        boolean flag = isSame(model, true);
        if (flag == false) {
            int num = 0;
            try {
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setString(1, model.getTypeName());
                pstm.setInt(2, model.getTypeCount());
                pstm.setString(3, model.getTypeFlag());
                pstm.setString(4, model.getTypeYears());
                num = pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (num > 0) {
                return "Ok";
            } else {
                return "Err";
            }
        } else {
            return "Same";
        }
    }

    @Override
    public String update(Type model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        boolean flag = isSame(model, false);
        if (flag == true) {
            return "Same";
        }
        sqlStr.append("update pa_type set ");
        if (model.getTypeName() != null && !model.getTypeName().equals("")) {
            sqlStr.append(" type_name='" + model.getTypeName() + "'");
        }
        if (model.getTypeCount() != null) {
            sqlStr.append(" ,type_count=" + model.getTypeCount());
        }
        if (model.getTypeFlag() != null && !model.getTypeFlag().equals("")) {
            sqlStr.append(" ,type_flag='" + model.getTypeFlag() + "'");
        }
        if (model.getTypeYears() != null && !model.getTypeYears().equals("")) {
            sqlStr.append(" ,type_years='" + model.getTypeYears() + "'");
        }
        sqlStr.append(" where type_id=? ");
        int num = 0;
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, model.getTypeId());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public String delete(Type model) {
        return null;
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("delete from pa_type where type_id=? ");
        conn = db.getConnection();
        int num = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public List<Type> getAllData(Type model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Type> list = new ArrayList<Type>();
        sqlStr.append(" SELECT * from pa_type ");
        sqlStr.append("where 1=1 ");
        if (model.getTypeName() != null && !model.getTypeName().equals("")) {
            sqlStr.append("and type_name='" + model.getTypeName() + "' ");
        }
        if (model.getTypeFlag() != null && !model.getTypeFlag().equals("")) {
            sqlStr.append(" and type_flag='" + model.getTypeFlag() + "' ");
        }
        if (model.getTypeYears() != null && !model.getTypeYears().equals("")) {
            sqlStr.append(" and type_years='" + model.getTypeYears() + "' ");
        }
        sqlStr.append(" ORDER BY type_stmp desc,type_id asc,type_name asc ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Type md = new Type();
                md.setTypeId(rs.getLong("type_id"));
                md.setTypeName(rs.getString("type_name"));
                md.setTypeCount(rs.getInt("type_count"));
                md.setTypeFlag(rs.getString("type_flag").equals("0") ? "启用" : "禁用");
                md.setTypeYears(rs.getString("type_years"));
                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Type findById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" SELECT * from pa_type ");
        sqlStr.append("where 1=1 and type_id=? ");
        conn = db.getConnection();
        Type md = new Type();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            rs = pstm.executeQuery();
            while (rs.next()) {
                md.setTypeId(rs.getLong("type_id"));
                md.setTypeName(rs.getString("type_name"));
                md.setTypeCount(rs.getInt("type_count"));
                md.setTypeFlag(rs.getString("type_flag").equals("0") ? "启用" : "禁用");
                md.setTypeYears(rs.getString("type_years"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return md;
    }

    @Override
    public Type find(Type model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" SELECT * from pa_type ");
        sqlStr.append("where 1=1 ");
        if (model.getTypeName() != null && !model.getTypeName().equals("")) {
            sqlStr.append("and type_name='" + model.getTypeName() + "' ");
        }
        if (model.getTypeFlag() != null && !model.getTypeFlag().equals("")) {
            sqlStr.append(" and type_flag='" + model.getTypeFlag() + "' ");
        }
        if (model.getTypeYears() != null && !model.getTypeYears().equals("")) {
            sqlStr.append(" and type_years='" + model.getTypeYears() + "' ");
        }

        conn = db.getConnection();
        Type md = new Type();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                md.setTypeId(rs.getLong("type_id"));
                md.setTypeName(rs.getString("type_name"));
                md.setTypeCount(rs.getInt("type_count"));
                md.setTypeFlag(rs.getString("type_flag").equals("0") ? "启用" : "禁用");
                md.setTypeYears(rs.getString("type_years"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return md;
    }

    @Override
    public boolean isSame(Type model, boolean sflag) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append(" select * from pa_type where type_name=? ");
        if (sflag == false) {
            sqlStr.append("and type_id!='" + model.getTypeId() + "' ");
        }
        conn = db.getConnection();
        boolean flag = false;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setString(1, model.getTypeName());
            rs = pstm.executeQuery();
            if (rs.next()) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public List<Type> getAllDataByPage(Type model, Page page) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Type> list = new ArrayList<>();

        sqlStr.append("SELECT * FROM pa_type WHERE 1=1 ");
        if (model.getTypeName() != null && !model.getTypeName().isEmpty()) {
            sqlStr.append("AND type_name LIKE ? ");
        }
        if (model.getTypeFlag() != null && !model.getTypeFlag().isEmpty()) {
            sqlStr.append("AND type_flag = ? ");
        }
        sqlStr.append("ORDER BY type_stmp DESC, type_id ASC, type_name ASC ");
        sqlStr.append("LIMIT ?, ?");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            int index = 1;
            if (model.getTypeName() != null && !model.getTypeName().isEmpty()) {
                pstm.setString(index++, "%" + model.getTypeName() + "%");
            }
            if (model.getTypeFlag() != null && !model.getTypeFlag().isEmpty()) {
                pstm.setString(index++, model.getTypeFlag());
            }
            pstm.setInt(index++, (page.getCurrentPage() - 1) * page.getCount());
            pstm.setInt(index++, page.getCount());

            rs = pstm.executeQuery();
            while (rs.next()) {
                Type md = new Type();
                md.setTypeId(rs.getLong("type_id"));
                md.setTypeCount(rs.getInt("type_count"));
                md.setTypeName(rs.getString("type_name"));
                md.setTypeFlag(rs.getString("type_flag").equals("0") ? "启用" : "禁用");
                md.setTypeYears(rs.getString("type_years"));
                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }


    @Override
    public int getCount(Type model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT COUNT(*) FROM pa_type WHERE 1=1 ");
        if (model.getTypeName() != null && !model.getTypeName().isEmpty()) {
            sqlStr.append("AND type_name LIKE ? ");
        }
        if (model.getTypeFlag() != null && !model.getTypeFlag().isEmpty()) {
            sqlStr.append("AND type_flag = ? ");
        }

        conn = db.getConnection();
        int total = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            int index = 1;
            if (model.getTypeName() != null && !model.getTypeName().isEmpty()) {
                pstm.setString(index++, "%" + model.getTypeName() + "%");
            }
            if (model.getTypeFlag() != null && !model.getTypeFlag().isEmpty()) {
                pstm.setString(index++, model.getTypeFlag());
            }
            rs = pstm.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }


    @Override
    public List<Type> getAllky() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Type> list = new ArrayList<>();

        sqlStr.append("SELECT type_id, type_name FROM pa_type WHERE type_flag = '0' ORDER BY type_name ASC");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Type md = new Type();
                md.setTypeId(rs.getLong("type_id"));
                md.setTypeName(rs.getString("type_name"));
                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

}